package cn.jekun.shop.web.service;

import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import cn.jekun.component.base.bean.Page;
import cn.jekun.component.base.exceptions.BusinessException;
import cn.jekun.component.base.utils.SqlUtis;
import cn.jekun.component.base.validator.annotation.Num;
import cn.jekun.component.base.validator.annotation.Security;
import cn.jekun.component.core.Constants;
import cn.jekun.component.core.bean.gen.ErpShop;
import cn.jekun.component.core.bean.gen.ErpShoptype;
import cn.jekun.component.core.bean.gen.RsUserShop;
import cn.jekun.component.core.service.gen.ErpShopService;
import cn.jekun.component.core.service.gen.RsUserShopService;
import cn.jekun.shop.common.service.ShopBaseService;

@Service(ShopService.BEAN_NAME)
public class ShopService extends ShopBaseService {
	
	
	@Resource(name = RsUserShopService.BEAN_NAME)
	protected RsUserShopService rsUserShopService;
	
	
	@Resource(name = ErpShopService.BEAN_NAME)
	protected ErpShopService erpShopService;
	
	public static final String BEAN_NAME = "cn.jekun.shop.web.service.ShopService";

	@Override
	public String getNamespace() {
		return "SHOP_SHOPMESSAGE";
	}
	
	
	
	
	/**
	 * 商家列表
	 * @param pageSize
	 * @param currentPage
	 * @param typeId
	 * @param lng
	 * @param lat
	 * @param cityId
	 * @param shopName
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public JSONObject queryList(@Num(length=10,message="每页条数不是有效数字格式") String pageSize,@Num(length=10,message="当前页面不是有效数字格式") String currentPage,@Security String distance,
			@Security String typeId, @Security String lng, @Security String lat, @ErpShop.AnnoCityId String cityId,@ErpShop.AnnoShopName String shopName,@ErpShop.AnnoRegionId String regionId,String sort,@ErpShop.AnnoBusinessStatus String businessStatus) {
		JSONObject jsonObject = new JSONObject();
		jsonObject.put("rflag", "0");//默认是显示不是推荐的商家
		Page page = new Page();
		List resultList = new ArrayList();
		Map formMap = new HashMap();
		if (StringUtils.isBlank(pageSize)) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"页大小不能为空");
		}
		if (StringUtils.isBlank(currentPage)) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"当前页不能为空");
		}
	/*	if (StringUtils.isBlank(cityId)) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"当前开通城市不能为空");
		}*/
		page.setPageSize(Integer.parseInt(pageSize));
		page.setPageNumber(Integer.parseInt(currentPage));
		formMap.put("typeId", typeId);
		formMap.put("cityId", cityId);
		formMap.put(ErpShop.COL_SHOP_TYPE_ID, typeId);
		formMap.put(ErpShop.COL_CITY_ID, cityId);
		formMap.put(ErpShop.COL_REGION_ID, regionId);
		formMap.put(ErpShop.COL_SHOP_NAME, shopName);
		formMap.put("distance", distance);
		formMap.put(ErpShop.COL_BUSINESS_STATUS, businessStatus);
		String order="";
		if("0".equals(sort)){
			order = "distance asc";
		}
		//起送金额最少
		else if("1".equals(sort)){
			order = "businessStatus asc, minAmount asc , seoweight desc nulls last";
		}
		//默认
		else if("2".equals(sort)){
			order = "businessStatus asc, seoweight desc nulls last, distance asc";
		}
		//商家满意度
		else if("3".equals(sort)){
			order = "businessStatus asc, score desc nulls last , seoweight desc nulls last";
		}
		//TODO 销量高到低
		else if("4".equals(sort)){
			order = "";
		}
		//TODO 送货时间小到大
		else if("5".equals(sort)){
			order = "";
		}
		
		//resultList = this.list(this.getStatementName("list2"), formMap, page);
		
		
		//如果经纬度不为空则使用有带距离结果的算法
		if(StringUtils.isNotBlank(lat) && StringUtils.isNotBlank(lng)){
			
			formMap.put("lng", lng);
			formMap.put("lat", lat);
			SqlUtis.prepareSql(formMap, 
					StringUtils.isBlank(typeId)?"":" and a.shop_type_id in (select shop_type_id from erp_shoptype where shop_type_id = '"+typeId+"' or parent_id='"+typeId+"')",
					SqlUtis.getSQL(formMap, "a." + ErpShop.COL_CITY_ID, Types.VARCHAR,SqlUtis.EQ), 
					SqlUtis.getSQL(formMap, "a." + ErpShop.COL_REGION_ID, Types.VARCHAR,SqlUtis.EQ),
					SqlUtis.getSQL(formMap, "q." + "distance", Types.VARCHAR, SqlUtis.LE),
					SqlUtis.getSQL(formMap, "a."+ErpShop.COL_SHOP_NAME,Types.VARCHAR,SqlUtis.LIKE),
					SqlUtis.getSQL(formMap, "a." + ErpShop.COL_BUSINESS_STATUS, Types.VARCHAR,SqlUtis.EQ),
					//" and a.BUSINESS_STATUS <> '4'  AND NVL2 (DECODE (SIGN (a.range - q.distance), 1, '1', 0, '1',-1, '0'),DECODE (SIGN (a.range - q.distance),1, '1', 0, '1', -1, '0'), 0) = '1' ",//结业
					" and( a.BUSINESS_STATUS <> '4' and a.business_status <> '3') and (sign(a.range-q.distance))>=0",//结业

					
					SqlUtis.orderBy(order));
			resultList = this.list(formMap, page);
		}else{
			//如果经纬度为空则查询结果不带距离算法
			SqlUtis.prepareSql(formMap, 
					StringUtils.isBlank(typeId)?"":" and a.shop_type_id in (select shop_type_id from erp_shoptype where shop_type_id = '"+typeId+"' or parent_id='"+typeId+"')",
					SqlUtis.getSQL(formMap, "a." + ErpShop.COL_CITY_ID, Types.VARCHAR,SqlUtis.EQ),
					SqlUtis.getSQL(formMap, "a." + ErpShop.COL_REGION_ID, Types.VARCHAR,SqlUtis.EQ),
					SqlUtis.getSQL(formMap, "a."+ErpShop.COL_SHOP_NAME,Types.VARCHAR,SqlUtis.LIKE),
					SqlUtis.getSQL(formMap, "a." + ErpShop.COL_BUSINESS_STATUS, Types.VARCHAR,SqlUtis.EQ),
					" and ( a.BUSINESS_STATUS <> '4' and a.business_status <> '3')"//结业
					);
			resultList = this.list(this.getStatementName("list2"), formMap, page);
		}
		
	/*	if(resultList!=null){
			
			if(resultList.size()==0 && businessStatus.equals("1")){
				
				formMap = new HashMap();
				formMap.put(ErpShop.COL_RECOMMENDED, "1");
				formMap.put("lng", lng);
				formMap.put("lat", lat);
			
				SqlUtis.prepareSql(formMap, 	SqlUtis.getSQL(formMap, "a." + ErpShop.COL_BUSINESS_STATUS, Types.VARCHAR,SqlUtis.EQ),
						SqlUtis.getSQL(formMap, "a." + ErpShop.COL_RECOMMENDED, Types.VARCHAR,SqlUtis.EQ),SqlUtis.orderBy(order));
				resultList = this.list(formMap, page);
				jsonObject.put("rflag", "1");//显示推荐的商家
			}
			
		}*/
		
		JSONArray jsonArray = JSONArray.fromObject(resultList);
		jsonObject.put("shopList", jsonArray);
		jsonObject.put("totals", page.getTotalCount());
		jsonObject.put(Constants.Page.PAGE_NUMBER, page.getPageNumber());
		jsonObject.put(Constants.Page.PAGE_SIZE, page.getPageSize());
		jsonObject.put(Constants.Page.TOTAL_COUNT, page.getTotalCount());
		jsonObject.put(Constants.Page.TOTAL_PAGE, page.getPages());
		return executeRerutn(jsonObject);
	}

	public JSONObject showGoodsTypesByShopId(@ErpShop.AnnoShopId String shopId) {
		JSONObject jsonObject = new JSONObject();
		Page page = new Page();
		 List resultList = new ArrayList();
		 Map formMap = new HashMap();
		if (StringUtils.isBlank(shopId)) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"商家代码不能为空");
		}
		resultList = this
				.listBySql("select distinct c.merchandise_type_id,c.merchandise_type_name from ERP_INVENTORY a  "
						+ "left join ERP_MERCHANDISE b on(a.merchandise_id=b.merchandise_id) "
						+ "left join erp_merchandise_type c on(b.merchandise_type_id=c.merchandise_type_id) "
						+ "where b.shop_id='"
						+ shopId+"'");
		jsonObject.put("goodsTypeList", resultList);
		return executeRerutn(jsonObject);
	}
	
	public JSONObject getShopListByType(String lng,String lat,String shopType) {
		JSONObject jsonObject = new JSONObject();
		
		if (StringUtils.isBlank(lng)) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"经度不能为空");
		}
		
		if (StringUtils.isBlank(lat)) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"纬度不能为空");
		}
		
		if (StringUtils.isBlank(shopType)) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"商家类型不能为空");
		}
		
		
		//StringBuffer buffer=new StringBuffer();
		
		
		String sqlStr="";
		
		if(shopType.indexOf(",")>0){
			
			String[] shopTypeArray=shopType.split(",");
			
			for(int i=0;i<shopTypeArray.length;i++){
				
				int rowNum=0;
				
				if(shopTypeArray[i].equals("1")){
					
					rowNum=3;
				}else if(shopTypeArray[i].equals("2")){
					
					rowNum=4;
				}else if(shopTypeArray[i].equals("3")){
					
					rowNum=3;
				}else if(shopTypeArray[i].equals("4")){
					
					rowNum=3;
				}else if(shopTypeArray[i].equals("5")){
					
					rowNum=2;
				}else if(shopTypeArray[i].equals("50")){
					
					rowNum=4;
				}
				

			    		   
				//buffer.append("select * from ( select row_.*, rownum rownum_ from ( select a.shop_id shopId,case when a.score > 0  AND a.score < 1 then  nvl2(a.score, 0.5, 0.0)          when a.score > 1  AND a.score < 2 then  nvl2(a.score, 1.5, 0.0)         when a.score > 2  AND a.score < 3 then  nvl2(a.score, 2.5, 0.0)         when a.score > 3  AND a.score < 4 then  nvl2(a.score, 3.5, 0.0)         when a.score > 4  AND a.score < 5 then  nvl2(a.score, 4.5, 0.0)         else nvl2(a.score, a.score, 0.0)          end score,                      case when a.business_status != '2' then nvl2(a.business_status,'1','1') else nvl2(a.business_status, a.business_status, '1')          end businessStatus,        a.recommended recommended,     a.shop_name shopName,   a.longitude lng,   a.latitude lat,   a.range range,   a.logo_url logoUrl,   nvl2(a.min_amount, a.min_amount, 0)    minAmount,   a.address address,   b.shop_type_id shopTypeId,   b.shop_type_name shopTypeName ,   a.business_hours businessHours,   decode(sign((q.distance/1000)-10),1,'>10',0,'10',-1,to_char((q.distance / 1000),'fm999999990.999999999')) as distance,   nvl2(decode(sign(a.range-q.distance),1,'1',0,'1',-1,'0'),decode(sign(a.range-q.distance),1,'1',0,'1',-1,'0'),0) as see,   t.spe speId,   a.introduce introduce,       nvl2(a.seo_weight, a.seo_weight, 0) seoweight,NVL(b.parent_id,b.shop_type_id) parentId         from erp_shop a      left join erp_shoptype b   on (a.shop_type_id = b.shop_type_id)   left join (     select b.shop_id,listagg(c.spe_type_id,',') within GROUP (order by b.shop_id) spe     from erp_shop_spe b     join erp_spe_type c on (b.spe_type_id = c.spe_type_id)     group by b.shop_id   ) t on (a.shop_id=t.shop_id)   left join   (select round((6378137.0 *   ACOS(SIN(j.latitude *   ACOS(-1) / 180.0) *   SIN('"+lat+"' * ACOS(-1) / 180.0)+   COS(j.latitude *   ACOS(-1) / 180.0) *   COS('"+lat+"' * ACOS(-1) / 180.0) *   COS(j.longitude *   ACOS(-1) / 180.0 -   '"+lng+"' * ACOS(-1) / 180.0))),0) as   distance,j.shop_id from   erp_shop j ) q on   (q.shop_id=a.shop_id)   WHERE 1=1  and (a.shop_type_id = '"+shopTypeArray[i]+"' or a.shop_type_id in(select shop_type_id from erp_shoptype where parent_id='"+shopTypeArray[i]+"')) and (a.BUSINESS_STATUS = '1' OR a.BUSINESS_STATUS = '2') AND NVL2 (DECODE (SIGN (a.range - q.distance), 1, '1', 0, '1',-1, '0'),DECODE (SIGN (a.range - q.distance),1, '1', 0, '1', -1, '0'), 0) = '1'  order by q.distance  ) row_ ) where rownum_ <= "+rowNum+"");
				//sqlStr+="select * from ( select row_.*, rownum rownum_ from ( select a.shop_id shopId,case when a.score > 0  AND a.score < 1 then  nvl2(a.score, 0.5, 0.0)          when a.score > 1  AND a.score < 2 then  nvl2(a.score, 1.5, 0.0)         when a.score > 2  AND a.score < 3 then  nvl2(a.score, 2.5, 0.0)         when a.score > 3  AND a.score < 4 then  nvl2(a.score, 3.5, 0.0)         when a.score > 4  AND a.score < 5 then  nvl2(a.score, 4.5, 0.0)         else nvl2(a.score, a.score, 0.0)          end score,                      case when a.business_status != '2' then nvl2(a.business_status,'1','1') else nvl2(a.business_status, a.business_status, '1')          end businessStatus,        a.recommended recommended,     a.shop_name shopName,   a.longitude lng,   a.latitude lat,   a.range range,   a.logo_url logoUrl,   nvl2(a.min_amount, a.min_amount, 0)    minAmount,   a.address address,   b.shop_type_id shopTypeId,   b.shop_type_name shopTypeName ,   a.business_hours businessHours,   decode(sign((q.distance/1000)-10),1,'>10',0,'10',-1,to_char((q.distance / 1000),'fm999999990.999999999')) as distance,   nvl2(decode(sign(a.range-q.distance),1,'1',0,'1',-1,'0'),decode(sign(a.range-q.distance),1,'1',0,'1',-1,'0'),0) as see,   t.spe speId,   a.introduce introduce,       nvl2(a.seo_weight, a.seo_weight, 0) seoweight,NVL(b.parent_id,b.shop_type_id) parentId         from erp_shop a      left join erp_shoptype b   on (a.shop_type_id = b.shop_type_id)   left join (     select b.shop_id,listagg(c.spe_type_id,',') within GROUP (order by b.shop_id) spe     from erp_shop_spe b     join erp_spe_type c on (b.spe_type_id = c.spe_type_id)     group by b.shop_id   ) t on (a.shop_id=t.shop_id)   left join   (select round((6378137.0 *   ACOS(SIN(j.latitude *   ACOS(-1) / 180.0) *   SIN('"+lat+"' * ACOS(-1) / 180.0)+   COS(j.latitude *   ACOS(-1) / 180.0) *   COS('"+lat+"' * ACOS(-1) / 180.0) *   COS(j.longitude *   ACOS(-1) / 180.0 -   '"+lng+"' * ACOS(-1) / 180.0))),0) as   distance,j.shop_id from   erp_shop j ) q on   (q.shop_id=a.shop_id)   WHERE 1=1  and (a.shop_type_id = '"+shopTypeArray[i]+"' or a.shop_type_id in(select shop_type_id from erp_shoptype where parent_id='"+shopTypeArray[i]+"')) and (a.BUSINESS_STATUS = '1' OR a.BUSINESS_STATUS = '2') AND NVL2 (DECODE (SIGN (a.range - q.distance), 1, '1', 0, '1',-1, '0'),DECODE (SIGN (a.range - q.distance),1, '1', 0, '1', -1, '0'), 0) = '1'  order by q.distance  ) row_ ) where rownum_ <= "+rowNum+"";

				sqlStr += "select * from ( select row_.*, rownum rownum_ from ( select a.shop_id shopId,case when a.score > 0  AND a.score < 1 then  nvl2(a.score, 0.5, 0.0)   when a.score > 1  AND a.score < 2 then  nvl2(a.score, 1.5, 0.0)         when a.score > 2  AND a.score < 3 then  nvl2(a.score, 2.5, 0.0)         when a.score > 3  AND a.score < 4 then  nvl2(a.score, 3.5, 0.0)         when a.score > 4  AND a.score < 5 then  nvl2(a.score, 4.5, 0.0)         else nvl2(a.score, a.score, 0.0)          end score,                      case when a.business_status != '2' then nvl2(a.business_status,'1','1') else nvl2(a.business_status, a.business_status, '1')          end businessStatus,        a.recommended recommended,     a.shop_name shopName,   a.longitude lng,   a.latitude lat,   a.range range,   a.logo_url logoUrl,   nvl2(a.min_amount, a.min_amount, 0)    minAmount,   a.address address,   b.shop_type_id shopTypeId,   b.shop_type_name shopTypeName ,   a.business_hours businessHours,   decode(sign((q.distance/1000)-10),1,'>10',0,'10',-1,to_char((q.distance / 1000),'fm999999990.999999999')) as distance,   t.spe speId,   a.introduce introduce,       nvl2(a.seo_weight, a.seo_weight, 0) seoweight,NVL(b.parent_id,b.shop_type_id) parentId         from erp_shop a       left join erp_shoptype b    on (a.shop_type_id = b.shop_type_id)    left join (      select b.shop_id,listagg(c.spe_type_id,',') within GROUP (order by b.shop_id) spe      from erp_shop_spe b      join erp_spe_type c on (b.spe_type_id = c.spe_type_id)      group by b.shop_id    ) t on (a.shop_id=t.shop_id)    left join    (select pk_common.getDistance(j.latitude, j.longitude, "+lat+", "+lng+") as distance,j.shop_id from     erp_shop j ) q on    (q.shop_id=a.shop_id)   WHERE 1=1  and (a.shop_type_id = '"
						+ shopTypeArray[i]
						+ "' or a.shop_type_id in(select shop_type_id from erp_shoptype where parent_id='"
						+ shopTypeArray[i]
						+ "')) and (a.BUSINESS_STATUS = '1' OR a.BUSINESS_STATUS = '2')  and (sign(a.range-q.distance))>=0 order by distance asc ) row_ ) where rownum_ <= "+rowNum;
																						
				if(i!=shopTypeArray.length-1){
					//buffer.append(" union ");
					sqlStr+=" union ";
				}
				
			}
		}else{
			
			//buffer.append("select * from ( select row_.*, rownum rownum_ from ( select a.shop_id shopId,case when a.score > 0  AND a.score < 1 then  nvl2(a.score, 0.5, 0.0)          when a.score > 1  AND a.score < 2 then  nvl2(a.score, 1.5, 0.0)         when a.score > 2  AND a.score < 3 then  nvl2(a.score, 2.5, 0.0)         when a.score > 3  AND a.score < 4 then  nvl2(a.score, 3.5, 0.0)         when a.score > 4  AND a.score < 5 then  nvl2(a.score, 4.5, 0.0)         else nvl2(a.score, a.score, 0.0)          end score,                      case when a.business_status != '2' then nvl2(a.business_status,'1','1') else nvl2(a.business_status, a.business_status, '1')          end businessStatus,        a.recommended recommended,     a.shop_name shopName,   a.longitude lng,   a.latitude lat,   a.range range,   a.logo_url logoUrl,   nvl2(a.min_amount, a.min_amount, 0)    minAmount,   a.address address,   b.shop_type_id shopTypeId,   b.shop_type_name shopTypeName ,   a.business_hours businessHours,   decode(sign((q.distance/1000)-10),1,'>10',0,'10',-1,to_char((q.distance / 1000),'fm999999990.999999999')) as distance,   nvl2(decode(sign(a.range-q.distance),1,'1',0,'1',-1,'0'),decode(sign(a.range-q.distance),1,'1',0,'1',-1,'0'),0) as see,   t.spe speId,   a.introduce introduce,       nvl2(a.seo_weight, a.seo_weight, 0) seoweight,NVL(b.parent_id,b.shop_type_id) parentId         from erp_shop a      left join erp_shoptype b   on (a.shop_type_id = b.shop_type_id)   left join (     select b.shop_id,listagg(c.spe_type_id,',') within GROUP (order by b.shop_id) spe     from erp_shop_spe b     join erp_spe_type c on (b.spe_type_id = c.spe_type_id)     group by b.shop_id   ) t on (a.shop_id=t.shop_id)   left join   (select round((6378137.0 *   ACOS(SIN(j.latitude *   ACOS(-1) / 180.0) *   SIN('"+lat+"' * ACOS(-1) / 180.0)+   COS(j.latitude *   ACOS(-1) / 180.0) *   COS('"+lat+"' * ACOS(-1) / 180.0) *   COS(j.longitude *   ACOS(-1) / 180.0 -   '"+lng+"' * ACOS(-1) / 180.0))),0) as   distance,j.shop_id from   erp_shop j ) q on   (q.shop_id=a.shop_id)   WHERE 1=1  and (a.shop_type_id = '"+shopType+"' or a.shop_type_id in(select shop_type_id from erp_shoptype where parent_id='"+shopType+"')) and (a.BUSINESS_STATUS = '1' OR a.BUSINESS_STATUS = '2') AND NVL2 (DECODE (SIGN (a.range - q.distance), 1, '1', 0, '1',-1, '0'),DECODE (SIGN (a.range - q.distance),1, '1', 0, '1', -1, '0'), 0) = '1' order by q.distance  ) row_ ) where rownum_ <= 5");
		//	sqlStr+="select * from ( select row_.*, rownum rownum_ from ( select a.shop_id shopId,case when a.score > 0  AND a.score < 1 then  nvl2(a.score, 0.5, 0.0)          when a.score > 1  AND a.score < 2 then  nvl2(a.score, 1.5, 0.0)         when a.score > 2  AND a.score < 3 then  nvl2(a.score, 2.5, 0.0)         when a.score > 3  AND a.score < 4 then  nvl2(a.score, 3.5, 0.0)         when a.score > 4  AND a.score < 5 then  nvl2(a.score, 4.5, 0.0)         else nvl2(a.score, a.score, 0.0)          end score,                      case when a.business_status != '2' then nvl2(a.business_status,'1','1') else nvl2(a.business_status, a.business_status, '1')          end businessStatus,        a.recommended recommended,     a.shop_name shopName,   a.longitude lng,   a.latitude lat,   a.range range,   a.logo_url logoUrl,   nvl2(a.min_amount, a.min_amount, 0)    minAmount,   a.address address,   b.shop_type_id shopTypeId,   b.shop_type_name shopTypeName ,   a.business_hours businessHours,   decode(sign((q.distance/1000)-10),1,'>10',0,'10',-1,to_char((q.distance / 1000),'fm999999990.999999999')) as distance,   nvl2(decode(sign(a.range-q.distance),1,'1',0,'1',-1,'0'),decode(sign(a.range-q.distance),1,'1',0,'1',-1,'0'),0) as see,   t.spe speId,   a.introduce introduce,       nvl2(a.seo_weight, a.seo_weight, 0) seoweight,NVL(b.parent_id,b.shop_type_id) parentId         from erp_shop a      left join erp_shoptype b   on (a.shop_type_id = b.shop_type_id)   left join (     select b.shop_id,listagg(c.spe_type_id,',') within GROUP (order by b.shop_id) spe     from erp_shop_spe b     join erp_spe_type c on (b.spe_type_id = c.spe_type_id)     group by b.shop_id   ) t on (a.shop_id=t.shop_id)   left join   (select round((6378137.0 *   ACOS(SIN(j.latitude *   ACOS(-1) / 180.0) *   SIN('"+lat+"' * ACOS(-1) / 180.0)+   COS(j.latitude *   ACOS(-1) / 180.0) *   COS('"+lat+"' * ACOS(-1) / 180.0) *   COS(j.longitude *   ACOS(-1) / 180.0 -   '"+lng+"' * ACOS(-1) / 180.0))),0) as   distance,j.shop_id from   erp_shop j ) q on   (q.shop_id=a.shop_id)   WHERE 1=1  and (a.shop_type_id = '"+shopType+"' or a.shop_type_id in(select shop_type_id from erp_shoptype where parent_id='"+shopType+"')) and (a.BUSINESS_STATUS = '1' OR a.BUSINESS_STATUS = '2') AND NVL2 (DECODE (SIGN (a.range - q.distance), 1, '1', 0, '1',-1, '0'),DECODE (SIGN (a.range - q.distance),1, '1', 0, '1', -1, '0'), 0) = '1' order by q.distance  ) row_ ) where rownum_ <= 5";

			sqlStr += "select * from ( select row_.*, rownum rownum_ from ( select a.shop_id shopId,case when a.score > 0  AND a.score < 1 then  nvl2(a.score, 0.5, 0.0)   when a.score > 1  AND a.score < 2 then  nvl2(a.score, 1.5, 0.0)         when a.score > 2  AND a.score < 3 then  nvl2(a.score, 2.5, 0.0)         when a.score > 3  AND a.score < 4 then  nvl2(a.score, 3.5, 0.0)         when a.score > 4  AND a.score < 5 then  nvl2(a.score, 4.5, 0.0)         else nvl2(a.score, a.score, 0.0)          end score,                      case when a.business_status != '2' then nvl2(a.business_status,'1','1') else nvl2(a.business_status, a.business_status, '1')          end businessStatus,        a.recommended recommended,     a.shop_name shopName,   a.longitude lng,   a.latitude lat,   a.range range,   a.logo_url logoUrl,   nvl2(a.min_amount, a.min_amount, 0)    minAmount,   a.address address,   b.shop_type_id shopTypeId,   b.shop_type_name shopTypeName ,   a.business_hours businessHours,   decode(sign((q.distance/1000)-10),1,'>10',0,'10',-1,to_char((q.distance / 1000),'fm999999990.999999999')) as distance,   t.spe speId,   a.introduce introduce,       nvl2(a.seo_weight, a.seo_weight, 0) seoweight,NVL(b.parent_id,b.shop_type_id) parentId         from erp_shop a       left join erp_shoptype b    on (a.shop_type_id = b.shop_type_id)    left join (      select b.shop_id,listagg(c.spe_type_id,',') within GROUP (order by b.shop_id) spe      from erp_shop_spe b      join erp_spe_type c on (b.spe_type_id = c.spe_type_id)      group by b.shop_id    ) t on (a.shop_id=t.shop_id)    left join    (select pk_common.getDistance(j.latitude, j.longitude, "+lat+", "+lng+") as distance,j.shop_id from     erp_shop j ) q on    (q.shop_id=a.shop_id)   WHERE 1=1  and (a.shop_type_id = '"
					+ shopType
					+ "' or a.shop_type_id in(select shop_type_id from erp_shoptype where parent_id='"
					+ shopType
					+ "')) and (a.BUSINESS_STATUS = '1' OR a.BUSINESS_STATUS = '2')  and (sign(a.range-q.distance))>=0 order by distance asc ) row_ ) where rownum_ <= 5";
			
		}
		
		
		
		
		List resultList = this.listBySql("SELECT *FROM ( "+sqlStr+" ) ORDER BY distance");
		
		List listData=new ArrayList();
		
		List<String> container=new ArrayList<String>();
		
		//HashMap<String, Object> hashMap;
		
		for(int i=0;i<resultList.size();i++){
			Map map=(Map) resultList.get(i);
			if(!container.contains(map.get("SHOPTYPEID").toString())){
				container.add(map.get("SHOPTYPEID").toString());
				//hashMap=new HashMap<String, Object>();
				//hashMap.put("data", getChildType(map.get("SHOPTYPEID").toString(),resultList));
				listData.add(getChildType(map.get("SHOPTYPEID").toString(),resultList));
			}
		}
		
		
		jsonObject.put("shopList", listData);
		return executeRerutn(jsonObject);
	}
	
	
	public JSONArray getChildType(String parenrId,List resultList){
		
		JSONArray jsonArray=new JSONArray();
		for(int i=0;i<resultList.size();i++){
			Map map=(Map) resultList.get(i);
			if(parenrId.equals(map.get("SHOPTYPEID").toString())){
				JSONObject jsonObject=new JSONObject();

				jsonObject.put("shopId", map.get("SHOPID").toString());
				jsonObject.put("shopName", map.get("SHOPNAME").toString());
				jsonObject.put("parentId", map.get("PARENTID").toString());
				jsonObject.put("businessStatus", map.get("BUSINESSSTATUS").toString());
				jsonObject.put("logoUrl", String.valueOf(map.get("LOGOURL")));
				jsonObject.put("shopTypeId", parenrId);
				jsonObject.put("shopTypeName", String.valueOf(map.get("SHOPTYPENAME")));
				jsonArray.add(jsonObject);
			}
			
		}
		
		return jsonArray;
		
	}

	public JSONObject getIsOpenShop(String userId) {
		
		if (StringUtils.isBlank(userId)) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"用户代码不能为空");
		}
		
		JSONObject jsonObject = new JSONObject();
		List list=this.listBySql("select b.shop_id shopId,b.shop_name shopName from RS_USER_SHOP a  join ERP_SHOP b on(a.shop_id=b.shop_id)  where a.user_id='"+userId+"'");
		if(list!=null && list.size()>0){
			
			Map map=(Map) list.get(0);
			jsonObject.put("shopId", map.get("SHOPID"));
			jsonObject.put("shopName", map.get("SHOPNAME"));
		}else{
			jsonObject.put("shopId", "");
			jsonObject.put("shopName", "");
			
		}
		return executeRerutn(jsonObject);
	}
	
	
	public JSONObject isShopExits(String shopId) {
		
		if (StringUtils.isBlank(shopId)) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"商家代码不能为空");
		}
		
		JSONObject jsonObject = new JSONObject();

		List list=this.listBySql("select a.shop_id shopId from ERP_SHOP a  where a.shop_id='"+shopId+"' or a.url='"+shopId+"'");
		
		if(!list.isEmpty()){
			
			Map map=(Map) list.get(0);
			
			jsonObject.put("isExits", true);

			jsonObject.put("shopId",map.get("SHOPID"));
			
			
		}else{
			jsonObject.put("isExits", false);
			
		}
		return executeRerutn(jsonObject);
	}
	
	public JSONObject getShopNameById(String shopId) {
		
		JSONObject jsonObject = new JSONObject();
		
		if (StringUtils.isBlank(shopId)) {
			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"商家代码不能为空");
		}
		
		ErpShop erpShop=this.erpShopService.one(shopId);
		
		jsonObject.put("shopName",erpShop.getShopName());
		
		return executeRerutn(jsonObject);
	}
	
	
	/**
	 * 根据搜索地址查询商店名字
	 * @return
	 */
	 public JSONObject getShopIdByAddress(String address)
	 {
	 		JSONObject jsonObject = new JSONObject();
	 		if(StringUtils.isBlank(address))
	 		{
	 			throw new BusinessException(Constants.ERRORS.VALIDATE_ERROR_CODE,
					"商家代码不能为空");
	 		}
	 		
	 		ErpShop erpShop = this.erpShopService.one(address);
	 		
	 		jsonObject.put("shopId", erpShop.getShopId());
	 		
	 		return executeRerutn(jsonObject);
	 }
	 
	 
	 
}
